package table.col;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import table.Col;
import table.DataTable;
import table.Row;
import table.SetDataParamenters;
import table.SqlTable;
import table.inter.ISqlCol;
import table.node.BaseNode;
import table.node.DataNode;
import table.node.TableNode;

public class SqlResultA2BCol extends A2BCol {

	public void setDataByCols(List<Col> cols) {
	  this.clearData();
	  this.sqlQuery();
	}

	protected DataNode getColValueFromBTable(DataTable bTable,int aRowIndex){
	  List<Col> ACols = this.getTableColsByNames(getATable(), getAColNames());
	  List<Col> BCols = this.getTableColsByNames(getBTable(), getBColNames());
	  List<Col> A_BACols = this.getTableColsByNames(getA2BTable(), getA2BAColNames());
	  List<Col> A_BBCols = this.getTableColsByNames(getA2BTable(), getBColNames());
	  if(ACols != null && BCols != null && ACols.size() > 0){
		  DataNode node = new BaseNode();
		  int result = 0;
		  if(A_BACols == null || A_BBCols == null){
			  for(int bRowIndex =0; bRowIndex < BCols.get(0).getDataSize(); bRowIndex++){
				  boolean flag = false;
				  for(int colIndex = 0; colIndex < ACols.size(); colIndex++){
					  if(ACols.get(colIndex).getValue(aRowIndex).equals(BCols.get(colIndex).getValue(bRowIndex))){
						  flag = true;
					  } else {
						  flag = false;
						  break;
					  }
				  }
				  if(flag){
					  result++;
				  }
			  }
		  } else {
			  for(int bRowIndex =0; bRowIndex < BCols.get(0).getDataSize(); bRowIndex++){
				  boolean flag = false;
				  for(int a_bRowIndex = 0; a_bRowIndex < A_BBCols.get(0).getDataSize(); a_bRowIndex++){
					  for(int bColIndex = 0; bColIndex < BCols.size(); bColIndex++){
						  if(A_BBCols.get(bColIndex).getValue(a_bRowIndex).equals(BCols.get(bColIndex).getValue(bRowIndex))){
							  flag = true;
						  } else {
							  flag = false;
							  break;
						  }
					  }
					  if(flag == false){continue;}
					  for(int aColIndex = 0; aColIndex < ACols.size(); aColIndex++){
						  if(A_BACols.get(aColIndex).getValue(a_bRowIndex).equals(ACols.get(aColIndex).getValue(aRowIndex))){
							  flag = true;
						  } else {
							  flag = false;
							  break;
						  }
					  }
				  }
				  if(flag){
					  result++;
				  }
			  }
		  }
		  node.setNodeValue(result);
		  return node;
	  }
	  return null;
	}
	
	protected DataNode getColDataFromBTable(DataTable bTable){
		//count the BCols.size
		List<Col> bCols = this.getTableColsByNames(this.getBTable(), this.getDstColNames());
		DataNode node = new TableNode();
		node.setNodeValue(bCols.size());
		
		List<DataNode> subNodes = new ArrayList<DataNode>();
		for(Iterator<Col> colIt = bCols.iterator();colIt.hasNext();){
			Col tCol = colIt.next();
			
			DataNode subNode = new BaseNode();
			subNode.setNodeValue(tCol.getDescripes());
			
			List<DataNode> subSubNodes = new ArrayList<DataNode>();
			subSubNodes.addAll(tCol.getDataList());
			subNode.setSubNodes(subSubNodes);
			
			subNodes.add(subNode);
		}
		node.setSubNodes(subNodes);

		return node;
	}
	
	protected DataNode getColDataFromBTableList(DataTable bTable){
		//count the BCols.size
		List<Col> bCols = this.getTableColsByNames(this.getBTable(), this.getDstColNames());
		DataNode node = new BaseNode();
		node.setNodeValue(bCols.size());
		
		List<DataNode> subNodes = new ArrayList<DataNode>();
		for(int i=0; i < bCols.get(0).getDataSize(); i++){
			DataNode subNode = new BaseNode();
			subNode.setNodeValue("");
			List<DataNode> subSubNodes = new ArrayList<DataNode>();
			for(Iterator<Col> colIt = bCols.iterator();colIt.hasNext();){
				Col tCol = colIt.next();
				subSubNodes.add(tCol.getNode(i));
			}
			subNode.setSubNodes(subSubNodes);
			subNodes.add(subNode);
		}
		node.setSubNodes(subNodes);

		return node;
	}
	  
	protected List<Col> getTableColsByNames(DataTable table, List<String> names){
		  List<Col> cols = new ArrayList<Col>();
		  if(names == null || table == null) return null;
		  for(int i=0; i < names.size(); i++){
			  if(table.getColByName(names.get(i)) != null){
				  cols.add(table.getColByName(names.get(i)));
			  }
		  }
		  return cols;
	  }

	public boolean copyColsFromAtoB(List<Col> aCols, List<Col> bCols, int rowIndex){
		try{
			for(int colIndex=0; colIndex < aCols.size(); colIndex++){
				Col aCol = aCols.get(colIndex);
				Col bCol = bCols.get(colIndex);
				bCol.clearData();
				if(rowIndex < 0 ){
					for(int i=0; i < aCol.getDataSize(); i++){
						Object aColData = aCol.getValue(i);
						bCol.addData(aCol.setNode(aColData));
					}
				} else {
					Object aColData = aCol.getValue(rowIndex);
					bCol.addData(aCol.setNode(aColData));
				}
			}
		} catch(Exception e){
			e.printStackTrace();
		}
		return false;
	}
	
/**
 * Sql query data
 */
	private void sqlQuery(){
		String sqlString = "";
		List<Col> ACols = this.getTableColsByNames(getATable(), getAColNames());
		List<Col> BCols = this.getTableColsByNames(getBTable(), getBColNames());
		List<Col> A_BACols = this.getTableColsByNames(getA2BTable(), getA2BAColNames());
		List<Col> A_BBCols = this.getTableColsByNames(getA2BTable(), getBColNames());
		SqlTable bTable = null;
		SqlTable a2bTable = null;
		try{
			bTable = (SqlTable)this.getBTable();
			a2bTable = (SqlTable)this.getA2BTable();
		}catch(Exception e){
			e.printStackTrace();
			return;
		}

		if(ACols != null && BCols != null && ACols.size() > 0){
			if(A_BACols == null || A_BBCols == null){
				String bColsStr = "";
				String bColsNameStr = "";
				String filterStr = "";
				for(int i=0; i < BCols.size(); i++){
					ISqlCol bCol = (ISqlCol)BCols.get(i);
					bColsStr += " , " + bCol.getExpression() + " as " + bCol.getName() + " ";
					bColsNameStr += ", " +bCol.getName();
				}
				bColsNameStr = bColsNameStr.substring(1);
				filterStr += " and (";
				for(int j=0; j < ACols.get(0).getDataSize(); j++){
					if(j==0){
						filterStr += " (";
					} else {
						filterStr += " or (";
					}
					for(int i=0; i < ACols.size(); i++){
						ISqlCol aCol = (ISqlCol) ACols.get(i);
						ISqlCol bCol = (ISqlCol)BCols.get(i);
						if(i == 0){
							filterStr += " " + bCol.getName() + "='" + aCol.getValue(j)+"'"; 
						} else {
							filterStr += " and " + bCol.getName() + "='" + aCol.getValue(j)+"'"; 
						}
					}
					filterStr += ") ";
				}
				filterStr += ")";
				sqlString = "select  count(*) " + bColsStr + " from " 
							+ bTable.getSqlString() + " as " + bTable.getDataTableName() 
							+ " where 1=1 " + filterStr +" group by " + bColsNameStr + " ";

				ResultSet rs = bTable.sqlQuery(sqlString);
				try {
					List<DataNode> dataList = new ArrayList<DataNode>();
					for(int i=0; i < ACols.get(0).getDataSize();i++){
						dataList.add(null);
					}
					while(rs!=null && rs.next()){
						for(int j=0; j < ACols.get(0).getDataSize(); j++){
							boolean flag = false;
							for(int i=0; i < ACols.size(); i++){
								ISqlCol aCol = (ISqlCol)ACols.get(i);
								ISqlCol bCol = (ISqlCol)BCols.get(i);
								if(bCol.getColValue(rs.getString(bCol.getName())).equals(aCol.getValue(j))){
									flag = true; 
								} else {
									flag = false;
									break;
								}
							}
							DataNode node = new BaseNode();
							if(flag){
								node.setNodeValue(rs.getInt(1));
							} else {
								node.setNodeValue(0);
							}
							dataList.set(j, node);
						}
					}
					this.getDataList().addAll(dataList);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			} else {
				String a2bAColsStr = "";
				String a2bAColsNameStr = "";
				String filterStr = "";
				for(int i=0; i < A_BACols.size(); i++){
					ISqlCol a2bACol = (ISqlCol)A_BACols.get(i);
					a2bAColsStr += " , " + a2bACol.getExpression() + " as " + a2bACol.getName() + " ";
					if(i > 0){a2bAColsNameStr += ", ";}
					a2bAColsNameStr += a2bACol.getName();
				}
				filterStr += " and (";
				int dataSize = ACols.get(0).getDataSize();
				if(dataSize > 1000){
					for(int i=0; i < ACols.size(); i++){
						ISqlCol a2bACol = (ISqlCol)A_BACols.get(i);
						if(i==0){
							filterStr += " " + a2bACol.getName() + " is not null"; 
						} else {
							filterStr += " and " + a2bACol.getName() + " is not null";  
						}
					}
				} else {
					for(int j=0; j < dataSize; j++){
						if(j==0){
							filterStr += " (";
						} else {
							filterStr += " or (";
						}
						for(int i=0; i < ACols.size(); i++){
							ISqlCol aCol = (ISqlCol) ACols.get(i);
							ISqlCol a2bACol = (ISqlCol)A_BACols.get(i);
							if(i==0){
								filterStr += " " + a2bACol.getName() + "='" + aCol.getValue(j)+"'"; 
							} else {
								filterStr += " and " + a2bACol.getName() + "='" + aCol.getValue(j)+"'"; 
							}
						}
						filterStr += ") ";
					}
				}
				filterStr += ")";
				String joinStr = "";
				for(int i=0; i < BCols.size();i++){
					if(i > 0){joinStr += " and ";}
					joinStr += bTable.getDataTableName() + "." + BCols.get(i).getName() + "=" 
						+ a2bTable.getDataTableName() + "." + A_BBCols.get(i).getName();
				}
				sqlString = "select  count(*) " + a2bAColsStr + " from " 
					+ a2bTable.getSqlString() + " as " + a2bTable.getDataTableName() 
					+ " left join "+ bTable.getSqlString() + " as " + bTable.getDataTableName() 
					+ " on " + joinStr
					+ " where 1=1 " + filterStr +" group by " + a2bAColsNameStr + " ";

				ResultSet rs = bTable.sqlQuery(sqlString);
				try {
					List<DataNode> dataList = new ArrayList<DataNode>();
					for(int i=0; i < ACols.get(0).getDataSize();i++){
						dataList.add(null);
					}
					while(rs!=null && rs.next()){
						for(int j=0; j < ACols.get(0).getDataSize(); j++){
							boolean flag = false;
							for(int i=0; i < ACols.size(); i++){
								ISqlCol aCol = (ISqlCol)ACols.get(i);
								ISqlCol a2bACol = (ISqlCol)A_BACols.get(i);
								if(a2bACol.getColValue(rs.getString(a2bACol.getName())).equals(aCol.getValue(j))){
									flag = true; 
								} else {
									flag = false;
									break;
								}
							}
							if(flag){
								DataNode node = new BaseNode();
								node.setNodeValue(rs.getInt(1));
								dataList.set(j, node);
							}
						}
					}
					this.getDataList().addAll(dataList);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				
			}
			  
		}
	}
	
	public DataNode run(HttpServletRequest request){
		String action = request.getParameter("colAction");
		DataNode result = null;
		if(action.equals("query")){
			//query relation
			result = this.query(request);
		}
		if(action.equals("add")){
			//add relation
			result.setNodeValue("add");
		}
		if(action.equals("delete")){
			//delete relation
			result.setNodeValue("delete");
			
		}
		if(action.equals("clear")){
			//clear relation
			result.setNodeValue("clear");
		}
		return result;
	}
	
	public DataNode query(HttpServletRequest request){
		
		SetDataParamenters ATParameter = new SetDataParamenters();
		ATParameter.setQueryList(this.getATQueryFromRequest(request));
		if(ATParameter.getQueryList() != null && ATParameter.getQueryList().size() > 0){
			ATParameter.setPageSize(99999);
			this.getATable().setData(ATParameter);
			this.setDataByCols(null);
		} else {
			SetDataParamenters p = new SetDataParamenters(request);
			if(p.getPageSize() <=0 || p.getPageSize() > 10){
				p.setPageSize(10);
			}
			this.getBTable().setData(p);
		}
		return  this.getColDataFromBTable(this.getBTable());
	}
	
	protected  List<String[]> getATQueryFromRequest(HttpServletRequest request){
		List<String[]> s = new ArrayList<String[]>();
		String[] r = null;
		String colName = "";
		String colValue = "";
		try {
			List<Row> ATRowList = Row.getRowsFromResquest(request);
			if(ATRowList != null && ATRowList.size() > 0){
				List<String> AColNames = this.getAColNames();
				if(AColNames!=null && AColNames.size()>0){
					Row dataRow = ATRowList.get(0);
					for(int i=0; i < AColNames.size(); i++){
						colName = AColNames.get(i);
						if(dataRow.hasName(colName)){
							r = new String[3];
							r[0] = colName; 
							r[1] = (String) dataRow.getValueByName(colName);
							r[2] = "=";
							s.add(r);
						} else {
							s.clear();
							return s;
						}
					}
				}
			}
		} catch (Exception e) {
				e.printStackTrace();
		}
		return s;
	}
}
